// 学校管理部分接口
var express = require('express');
var date = require("silly-datetime");
var router = express.Router();
var sqlUtils = require('../sql/sqlUtil');
const Return = require("../util/utils");
const {
  sqlConnect
} = require('../sql/sqlUtil');
/* GET users listing. */
// 查询学校信息|模糊查询
router.get('/', function (req, res, next) {
  var page = req.query.page - 1;
  var limit = req.query.limit * 1;
  var begin = page * limit;
  if (req.query.searchParams == undefined) {
    // 获取数据总数
    sqlUtils.sqlConnect("select count(1) as count from school where is_del=0", function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data[0].count;
        var sql = "select * from school where is_del=0 order by 1 desc limit ?,?";
        sqlUtils.sqlConnect(sql, [begin, limit], function (err, data) {
          if (err) {
            console.log(err);
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  } else {
    var flag = 2;
    var result = JSON.parse(req.query.searchParams);
    var s_name = (result.s_name.length == 0) ? '%' : result.s_name,
      level = (result.level == undefined) ? '%' : result.level;
    // 计算表中符合条件信息数量
    sqlUtils.sqlConnect("select count(1) as count from school where is_del=0 and s_name like '%" + s_name + "%' and level like '%" + level + "%'", function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data[0].count;
        var sql = "select * from school where is_del=0 and s_name like '%" + s_name + "%' and level like '%" + level + "%' order by 1 desc limit ?,?";
        sqlUtils.sqlConnect(sql, [begin, limit], function (err, data) {
          if (err) {
            console.log(err)
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  }
});
// 修改数据
router.put('/', function (req, res, next) {
  var s_name = req.body.username,
    id = req.body.sid;
  var sql = "update school set s_name=? where id=?",
    sqlArr = [s_name, id];
  sqlUtils.sqlConnect(sql, sqlArr, function (err, data) {
    if (err) {
      var json = Return.setResult();
      json.msg = "修改失败";
      res.send(json);
    } else {
      var json = Return.setResult();
      json.msg = "修改成功";
      res.send(json);
    }
  })
})
// 删除学校信息
router.delete("/", function (req, res, next) {
  var str = req.body.id;
  var sql = "update school set is_del=1 where id in(" + str + ")";
  sqlUtils.sqlConnect(sql, function (err, data) {
    if (err) {
      var json = Return.setResult();
      json.msg = "删除失败";
      res.send(json);
    } else {
      var json = Return.setResult();
      json.msg = "删除成功";
      res.send(json);
    }
  })
})
// 添加学校信息
router.post("/", function (req, res, next) {
  try {
    var s_name = req.body.username;
  var grade = req.body.grade;
  var classnum = req.body.class;
  var time = date.format(new Date(), 'YYYY-MM-DD');
  var sql = null;
  var sqlArr = null;

  // 当年级选择是小学时
  if (grade == "l1") {
    sql = "insert into school (s_name,level,create_time,grade_num,class_num,is_del) values(?,?,?,?,?,0)";
    sqlArr=[s_name, "小学", time, 6, classnum];
  } else if (grade == "l2") { //当年级数选择为初一~初三
    sql = "insert into school (s_name,level,create_time,grade_num,class_num,is_del) values(?,?,?,?,?,0)";
    sqlArr=[s_name, "初中", time, 3, classnum];
  } else if (grade == "l3") { //当年级数选择为高一~高三
    sql = "insert into school (s_name,level,create_time,grade_num,class_num,is_del) values(?,?,?,?,?,0)";
    sqlArr=[s_name, "高中", time, 3, classnum];
  } else if (grade == "l4") { //当年级数选择为初一~高三
    sql = "insert into school (s_name,level,create_time,grade_num,class_num,is_del) values(?,?,?,?,?,0)";
    sqlArr=[s_name, "中学", time, 6, classnum];
  }
  sqlUtils.sqlConnect(sql,sqlArr,(err,data)=>{
    if (err) {
      return console.log(err);
    }
    res.send(Return.setResult());
  })
  } catch (error) {
    console.log(error);
  }
  
})

// 校验学校是否已存在
router.post("/regex", function (req, res, next) {
  var sname = req.body.s_name;
  sqlUtils.sqlConnect("select * from school where s_name=?",sname,function (err, data) {
    if (err) {
      console.log(err);
    } else {
      var json = Return.setResult();
      json.code = data.length;
      res.send(json);
    }
  })
})
module.exports = router;